package com.androidcourse.changeandget.Database;
import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.http.SslCertificate;
import android.widget.Toast;

import com.androidcourse.changeandget.User.Good;
import com.androidcourse.changeandget.User.User;

import java.sql.Blob;
import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {
    private static final int VERSION=1;
    private static final String DBNAME="Change.db";   //  创建数据库名叫 Users
    private Context mContext;
    private SQLiteDatabase db;
    public static  final String GID="GID";
    public static  final String Dgood="Dgood";
    public static  final String Egood="EGood";
    public static  final String Gname="Gname";
    public static  final String SID="SID";
    public static  final String ischange="Ischange";
    public static  final String time="Time";
    public static  final String hood="Hood";
    public static  final String image1="Image";


    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL("PRAGMA foreign_keys = ON;");
    }

    public DBHelper(Context context){
        super(context,DBNAME,null,VERSION);
        db = getReadableDatabase();
        mContext = context;
    }
    //创建数据库
    public void onCreate(SQLiteDatabase db){
        //创建密码表  pwd_tb
        db.execSQL("create table Student (SID varchar(10) primary key,Sname varchar(20),Pass varchar(20)," +
                "Sestate varchar(2),Stele varchar(11),Ssex varchar(2))");
        //创建收入表    user_tb
        db.execSQL("create table Good(GID INTEGER PRIMARY KEY, SID varchar(10)," +
                " Gname varchar(50),Hood varchar(20),Time varchar(20),IsChange boolean,EGood varchar(50),Dgood varchar(100),Image BLOB)");

    }
    //数据库版本更新
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion)
    {
        db.execSQL("drop table if exists Student");
        db.execSQL("drop table if exists Good");
        onCreate(db);
    }
    //Student表数据插入
    public boolean insertStudentData (String SID, String PASS, String Sestate, String Sname, String Ssex, String Stele)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("SID", SID);
        contentValues.put("Pass", PASS);
        contentValues.put("Sestate", Sestate);
        contentValues.put("Sname", Sname);
        contentValues.put("Ssex", Ssex);
        contentValues.put("Stele", Stele);
        long result = db.insert("Student",null, contentValues);
        if (result ==-1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    //修改Student表信息
    public boolean changeStudentData (String Sname,  String Stele, String SID)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("Sname", Sname);
        contentValues.put("Stele", Stele);
        int result=db.update("Student",contentValues,"SID  =?", new String[]{SID});
        if (result==-1){
            return false;
        }else{
            return true;
        }
    }


    @SuppressLint("Range")
    private Good[] ConvertToGoods(Cursor cursor){
        int resultCounts = cursor.getCount();
        if (resultCounts == 0 || !cursor.moveToFirst()){
            return null;
        }
        Good[] goods = new Good[resultCounts];
        for (int i = 0 ; i<resultCounts; i++){
            goods[i] = new Good();
            goods[i].setGID(cursor.getString(0));
            goods[i].setGname(cursor.getString(cursor.getColumnIndex(Gname))) ;
            goods[i].setDGood(cursor.getString(cursor.getColumnIndex(Dgood)));
            goods[i].setEGood(cursor.getString(cursor.getColumnIndex(Egood)));
            goods[i].setSID(cursor.getString(cursor.getColumnIndex(SID)));
            goods[i].setHood(cursor.getString(cursor.getColumnIndex(hood)));
            goods[i].setTime(cursor.getString(cursor.getColumnIndex(time)));
            goods[i].setChange(false);
            goods[i].setImage(cursor.getBlob(cursor.getColumnIndex(image1)));
            cursor.moveToNext();
        }
        return goods;
    }

    public Good[] getonedata(String gid){
        Cursor results = db.query("Good",new String[]{SID,GID,Dgood,Egood,Gname,hood,time,image1,ischange},"GID = ?", new String[]{gid},null,null,"GID DESC");
        return ConvertToGoods(results);
    }



    //Good表数据插入
    public boolean insertGoodData (Good goods)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(SID,goods.getSID());
        contentValues.put(Dgood,goods.getDGood());
        contentValues.put(Egood,goods.getEGood());
        contentValues.put(Gname,goods.getGname());
        contentValues.put(hood,goods.getHood());
        contentValues.put(time,goods.getTime());
        contentValues.put(image1,goods.getImage());
        contentValues.put(ischange,goods.isChange());
        long result = db.insert("Good",null, contentValues);
        if (result ==-1)
        {
            return false;
        }
        else
        {
            return true;
        }
    }

    //从Student表中按SID顺序读取数据
    public ArrayList<User> getAllDATA(){
        ArrayList<User> list = new ArrayList<User>();
        Cursor cursor = db.query("Student",null,null,null,null,null,"SID DESC");
        while(cursor.moveToNext()){
            @SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex("SID"));
            @SuppressLint("Range") String password = cursor.getString(cursor.getColumnIndex("Pass"));
            @SuppressLint("Range") String Sname = cursor.getString(cursor.getColumnIndex("Sname"));
            @SuppressLint("Range") String Sestate = cursor.getString(cursor.getColumnIndex("Sestate"));
            @SuppressLint("Range") String Stele = cursor.getString(cursor.getColumnIndex("Stele"));
            @SuppressLint("Range") String Ssex = cursor.getString(cursor.getColumnIndex("Ssex"));
            list.add(new User(name, password, Sname, Ssex, Stele, Sestate));
        }
        return list;
    }

    public ArrayList<Good> getAllGoodDATA(){
        ArrayList<Good> list = new ArrayList<>();
        Cursor cursor = db.query("Good",null,null,null,null,null,"GID DESC");
        while(cursor.moveToNext()){
            @SuppressLint("Range") String GID = cursor.getString(cursor.getColumnIndex("GID"));
            @SuppressLint("Range") String SID = cursor.getString(cursor.getColumnIndex("SID"));
            @SuppressLint("Range") String Gname = cursor.getString(cursor.getColumnIndex("Gname"));
            @SuppressLint("Range") boolean isChange = cursor.getInt(cursor.getColumnIndex("isChange"))>0;
            @SuppressLint("Range") String EGood = cursor.getString(cursor.getColumnIndex("EGood"));
            @SuppressLint("Range") String DGood = cursor.getString(cursor.getColumnIndex("DGood"));
            @SuppressLint("Range") byte[] image=cursor.getBlob(cursor.getColumnIndex("Image"));
            @SuppressLint("Range") String Time = cursor.getString(cursor.getColumnIndex("Time"));
            @SuppressLint("Range") String Sestate = cursor.getString(cursor.getColumnIndex("Sestate"));
            @SuppressLint("Range") String Hood = cursor.getString(cursor.getColumnIndex("Hood"));
            list.add(new Good(GID,SID,Gname,isChange,EGood,image,Sestate,Hood,DGood,Time));

        }
        return list;
    }

    public List<Good> readGood() {
        //Toast.makeText(this,"111",Toast.LENGTH_SHORT).show();
        List<Good> differentTypes = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();
        String[] selectionArgs = {"0"};
        Cursor cursor = db.query("Good",null,"isChange = ?", selectionArgs,null,null,"GID DESC");
        if(cursor.moveToFirst()) {
            do{
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("Gname"));
                @SuppressLint("Range") String description = cursor.getString(cursor.getColumnIndex("Dgood"));
                @SuppressLint("Range") String ID = cursor.getString(cursor.getColumnIndex("GID"));
                @SuppressLint("Range") byte[] picture = cursor.getBlob(cursor.getColumnIndex("Image"));
                Good commodity = new Good();
                commodity.setGname(title);
                commodity.setDGood(description);
                commodity.setImage(picture);
                commodity.setGID(ID);
                differentTypes.add(commodity);
            }while (cursor.moveToNext());
        }
        cursor.close();
        return differentTypes;
    }

    public void upgrade(String gid){
        SQLiteDatabase db=this.getWritableDatabase();
        db.delete("Good","GID =" + gid,null);
    }
}
